//
//  CYDatabase.m
//  CreditAddressBook
//
//  Created by Lee on 15/10/15.
//  Copyright © 2015年 Lee. All rights reserved.
//

#import "CYDatabase.h"
#import "CYDBConfigLogic.h"
#import "LELongSQLGenerator.h"
#import "CYCollection.h"
#import "MJExtension.h"
#import "FMResultSet+CYAddition.h"
#import "CYCallHistory.h"
#import "CYCallRecord.h"
#import <CoreTelephony/CTCall.h>
#import "LEUUID.h"
#import "CYCloudPerson.h"

@interface CYDatabase ()

@end

@implementation CYDatabase

- (NSString *)databaseName {
    return @"by_call_history.sqlite";
}

#pragma mark - Initialize
+ (instancetype)sharedDatabase {
    static CYDatabase *database = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        database = [[CYDatabase alloc] init];
    });
    return database;
}

- (void)createTables {
//    [self.dbQueue inDatabase:^(FMDatabase *db) {
//        //收藏表
//        [db executeUpdate:@"create table if not exists t_call_collection (id text primary key UNIQUE not null, phone text, name text, headimage_name text, create_time INTEGER, record_id INTEGER);"];
//        
//        [db executeUpdate:@"create table if not exists t_call_history (id text primary key UNIQUE not null, parentId text, status text, phone text, name text, duration INTEGER, create_time INTEGER, callHistory blob);"];
//        
//        //通话记录表并检查数据库版本
//        CYDBConfigLogic *config = [[CYDBConfigLogic alloc] init];
//        BOOL checkResult = [config checkDatabase:db newVersion:config.dbVersion];
//        if (!checkResult) {
//            CYLog(@"数据库版本检查失败");
//        }
//        
//    }];
    BOOL result = [self createTable:@"create table if not exists t_test (id text primary key UNIQUE not null, name text, status text, duration INTEGER);" tableName:@"t_test" version:6];
    if (!result) {
        NSAssert(0, @"t_test创建失败");
    }
    result = [self createTable:@"create table if not exists t_call_collection (id text primary key UNIQUE not null, phone text, name text, headimage_name text, create_time INTEGER, record_id INTEGER);" tableName:@"t_call_collection" version:1];
    if (!result) {
        NSAssert(0, @"t_call_collection创建失败");
    }
    result = [self createTable:@"create table if not exists t_call_history (id text primary key UNIQUE not null, parentId text, status text, phone text, name text, duration INTEGER, create_time INTEGER, callHistory blob);" tableName:@"t_call_history" version:1];
    if (!result) {
        NSAssert(0, @"t_call_history创建失败");
    }
}

//- (FMDatabaseQueue *)DBQueue {
//    return self.dbQueue;
//}

#pragma mark - 数据库操作
#pragma mark - 收藏
- (BOOL)addCollection:(CYCollection *)collection {
    if ([collection.phone length] == 0) {
        NSParameterAssert(collection.phone);
        return false;
    }
    if (!collection.collectionId) {
        collection.collectionId = [LEUUID UUID];
    }
    //设置model——>字典时是否调用override方法
    [CYCollection referenceReplacedKeyWhenCreatingKeyValues:YES];
    
    collection.createTime = [NSString stringWithFormat:@"%@", @((int)[[NSDate date] timeIntervalSince1970])];
    __block BOOL result = false;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        NSUInteger totalCount = [db intForQuery:@"SELECT COUNT (id) FROM t_call_collection WHERE id = ?", collection.collectionId];
        if (totalCount > 0) {
            [LELongSQLGenerator generateSQLForUpdating:collection.keyValues tableName:@"t_call_collection" completion:^(NSString *sql, NSArray *arguments) {
                result = [db executeUpdate:sql withArgumentsInArray:arguments];
                if (!result) {
                    CYLog(@"%d : %@", [db lastErrorCode], [db lastErrorMessage]);
                }
            }];
        } else {
            [LELongSQLGenerator generateSQLForInserting:collection.keyValues tableName:@"t_call_collection" completion:^(NSString *sql, NSArray *arguments) {
                result = [db executeUpdate:sql withArgumentsInArray:arguments];
                if (!result) {
                    CYLog(@"%d : %@", [db lastErrorCode], [db lastErrorMessage]);
                }
            }];
        }
    }];
    return result;
}

- (BOOL)deleteCollectionWithId:(NSString *)collectionId {
    if (!collectionId || collectionId.trim.length == 0) {
        return false;
    }
    
    __block BOOL result = false;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        result = [db executeUpdate:@"DELETE FROM t_call_collection WHERE id = ?", collectionId];
    }];
    return result;
}

/**
 *  根据电话号码删除一条记录
 *
 *  @param phone 电话号码
 *
 *  @return
 */
- (BOOL)deleteCollectionWithPhoneNum:(NSString *)phone {
    if (!phone || phone.trim.length == 0) {
        return false;
    }
    __block BOOL result = false;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        result = [db executeUpdate:@"DELETE FROM t_call_collection WHERE phone = ?", phone];
    }];
    
    return result;
}

- (NSArray *)collectionWithRecordId:(NSString *)recordId {
    NSMutableArray *results = [[NSMutableArray alloc] init];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *s = [db executeQuery:@"SELECT * FROM t_call_collection WHERE record_id = ?", recordId];
        while ([s next]) {
            CYCollection *collection = [s toCollection];
            [results addObject:collection];
        }
    }];
    return results;
}

- (NSArray *)collections {
    NSMutableArray *results = [[NSMutableArray alloc] init];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *s = [db executeQuery:@"SELECT * FROM t_call_collection"];
        while ([s next]) {
            CYCollection *collection = [s toCollection];
            [results addObject:collection];
        }
    }];
    return results;
}

- (NSArray *)collectionsWithPhone:(NSString *)phone {
    NSMutableArray *results = [[NSMutableArray alloc] init];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *s = [db executeQuery:@"SELECT * FROM t_call_collection WHERE phone = ?", phone];
        while ([s next]) {
            CYCollection *collection = [s toCollection];
            [results addObject:collection];
        }
    }];
    return results;
}

/**
 *  根据电话号码和名称删除收藏记录
 *  persons CYCloudPerson
 */
- (BOOL)deleteCollectionsWithPersons:(NSArray *)persons {
    BOOL result = NO;
    for (CYCloudPerson *person in persons) {
        NSArray *arr = [self collectionsWithPhone:person.phone.firstObject andName:person.name];
        if (arr && arr.count) {
            result = [self deleteCollectionWithPhoneNum:person.phone.firstObject];
            result = [self deleteCollectionsWithPersons:persons.firstObject];
        }
    }
    return result;
}

/**
 *  根据电话号码和名称查询收藏记录
 */
- (NSArray *)collectionsWithPhone:(NSString *)phone andName:(NSString *)name {
    NSMutableArray *results = [[NSMutableArray alloc] init];
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *s = [db executeQuery:@"SELECT * FROM t_call_collection WHERE phone = ? and name = ?", phone, name];
        while ([s next]) {
            CYCollection *collection = [s toCollection];
            [results addObject:collection];
        }
    }];
    return results;
}

#pragma mark - 通话记录
/**
 *  保存一条通话记录
 *
 *  @param history
 */
- (void)addCallHistory:(CYCallHistory *)history {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        //需要存储的数据
        NSString *name = history.name;
        if (!name || [name isEqualToString:CYPhoneNameNil]) {
            name = @"";
        }
        
        NSData *data = [NSKeyedArchiver archivedDataWithRootObject:history];
        
        //存数据
        [db executeUpdate:@"insert or replace into t_call_history (id, parentId, status, phone, name, duration, create_time, callHistory) values(?, ?, ?, ?, ?, ?, ?, ?);", history.callId, history.parentId, history.status, history.phone, name, [NSNumber numberWithFloat:history.duration], [NSNumber numberWithDouble:[NSDate date].timeIntervalSince1970], data];
        
    }];
}

/**
 *  保存多条通话记录
 *
 *  @param historys
 */
- (void)addCallHistorys:(NSArray *)historys {
    for (CYCallHistory *history in historys) {
        [self addCallHistory:history];
    }
}

/**
 *  更新N条记录
 *
 *  @param historys
 */
- (void)updateCallHistorys:(NSArray *)historys {
    for (CYCallHistory *call in historys) {
        [self updateCallHistory:call];
    }
}

/**
 *  更新一条记录
 *
 *  @param history
 */
- (void)updateCallHistory:(CYCallHistory *)history {
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        //需要存储的数据
        NSString *name = history.name;
        if (!name || [name isEqualToString:CYPhoneNameNil]) {
            name = @"";
        }
        
        NSData *data = [NSKeyedArchiver archivedDataWithRootObject:history];
        
        //更新数据
        [db executeUpdate:@"update t_call_history set duration = ?, status = ?, callHistory = ?, name = ? where id = ?", [NSNumber numberWithFloat:history.duration], history.status, data, name, history.callId];
        
    }];
}

/**
 *  根据条件读取通话记录
 *
 *
 *  @return
 */
- (NSArray *)callHistorysWithParam:(NSDictionary *)param {
    __block NSMutableArray *arr = nil;
    
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        arr = [NSMutableArray array];
        
        FMResultSet *rs = nil;
        if (param[@"callId"]) {
            rs = [db executeQuery:@"select * from t_call_history where id = ?", param[@"callId"]];
        }else if (param[@"orderByCallNum"]) {
            if (param[@"time"]) {
                rs = [db executeQuery:@"select *, count(*) num from t_call_history where parentId = '0' and create_time > ? group by phone order by count(*) desc", param[@"time"]];
            } else {
                rs = [db executeQuery:@"select *, count(*) num from t_call_history where parentId = '0' group by phone order by count(*) desc"];
            }
            
        } else if (param[@"phone"]) {
            if (param[@"time"]) {
                rs = [db executeQuery:@"select * from t_call_history where parentId = '0' and phone = ? and create_time > ? order by create_time desc", param[@"phone"], param[@"time"]];
            } else {
                rs = [db executeQuery:@"select * from t_call_history where parentId = '0' and phone = ? order by create_time desc", param[@"phone"]];
            }
        } else if (param[@"parentId"]) {
            rs = [db executeQuery:@"select * from t_call_history where parentId = ? order by create_time desc;", param[@"parentId"]];
        } else if (param[@"orderByCallDuration"]) {
            if (param[@"time"]) {
                rs = [db executeQuery:@"select *, sum(duration) duration_sum from t_call_history where parentId = '0' and create_time > ? group by phone order by sum(duration) desc", param[@"time"]];
            } else {
                rs = [db executeQuery:@"select *, sum(duration) duration_sum from t_call_history where parentId = '0' group by phone order by sum(duration) desc"];
            }
        } else if (param[@"queryDurationAndTimes"]) {
            if (param[@"time"]) {
                rs = [db executeQuery:@"select count(*) num, sum(duration) total_duration, * from t_call_history where parentId = '0' and create_time > ?", param[@"time"]];
            } else {
                rs = [db executeQuery:@"select count(*) num, sum(duration) total_duration, * from t_call_history where parentId = '0'"];
            }
        } else if (param[@"queryDialDuration"]) {
            rs = [db executeQuery:@"select sum(duration) all_duration, * from t_call_history where parentId = '0'"];
        } else if (param[@"queryComeOutDurationAndTimes"]) {
            rs = [db executeQuery:@"select count(*) num, sum(duration) comeout_duration, * from t_call_history where parentId = '0' and status = 'CTCallStateDialing' and duration > 0 "];
        }else if (param[@"timeAsc"]) {
            rs = [db executeQuery:@"select * from t_call_history where parentId = '0' order by create_time"];
        } else if (param[@"dialedAndTimes"]) {
            rs = [db executeQuery:@"select count(*) num, * from t_call_history where parentId = '0' and status = 'CTCallStateDialing' and duration > 0 "];
        } else {
            rs = [db executeQuery:@"select * from t_call_history where parentId = '0' group by phone order by create_time desc"];
        }
        
        while (rs.next) {
            NSString *callId = [rs stringForColumn:@"id"];
            NSString *status = [rs stringForColumn:@"status"];
            NSString *parentId = [rs stringForColumn:@"parentId"];
            NSString *phone = [rs stringForColumn:@"phone"];
            NSString *name = [rs stringForColumn:@"name"];
            CGFloat duration = [rs doubleForColumn:@"duration"];
            CGFloat createTime = [rs doubleForColumn:@"create_time"];
            int num = 0;
            if (param[@"orderByCallNum"] || param[@"queryDurationAndTimes"] || param[@"queryComeOutDurationAndTimes"] || param[@"dialedAndTimes"]) {
                num = [rs intForColumn:@"num"];
            }
            CGFloat durationSum = .0;
            if (param[@"orderByCallDuration"]) {
                durationSum = [rs doubleForColumn:@"duration_sum"];
            } else if (param[@"queryDurationAndTimes"]) {
                durationSum = [rs doubleForColumn:@"total_duration"];
            } else if (param[@"queryComeOutDurationAndTimes"]) {
                durationSum = [rs doubleForColumn:@"comeout_duration"];
            }
            
            NSData *data = [rs dataForColumn:@"callHistory"];
            CYCallRecord *callRecord = [[CYCallRecord alloc] init];
            if (data) {
                CYCallHistory *callHistory = [NSKeyedUnarchiver unarchiveObjectWithData:data];
                callRecord.callHistory = callHistory;
                callHistory.headImageName = callHistory.headImageName;
                callHistory.thumbnail = callHistory.thumbnail;
                
                callHistory.callId = callId;
                callHistory.status = status;
                callHistory.parentId = parentId;
                callHistory.phone = phone;
                callHistory.name = name;
                callHistory.duration = duration;
                callHistory.createTime = createTime;
                callHistory.num = num;
                callHistory.durationSum = durationSum;
            }
            
            [arr addObject:callRecord];
        }
        
    }];
    return arr;
}

/**
 *  更新通话时长
 *
 *  @param callHistory
 */
- (BOOL)updateDuration:(CYCallHistory *)callHistory {
    NSArray *arr = [self callHistorysWithParam:@{@"parentId": callHistory.parentId}];
    CGFloat startTime = 0, endTime = 0;
    for (CYCallRecord *callRecord in arr) {
        CYCallHistory *call = callRecord.callHistory;
        if ([call.status isEqualToString:CTCallStateConnected]) {
            startTime = call.createTime;
        } else if ([call.status isEqualToString:CTCallStateDisconnected]) {
            endTime = call.createTime;
        }
    }
    if (startTime != 0) {
        callHistory.duration = endTime - startTime;
    }
    NSString *name = callHistory.name;
    if (!name || [name isEqualToString:CYPhoneNameNil]) {
        name = @"";
    }
    __block BOOL success = NO;
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        success = [db executeUpdate:@"update t_call_history set duration = ?, name = ? where id = ?;", [NSNumber numberWithFloat:callHistory.duration], name, callHistory.parentId];
    }];
    return success;
}
@end
